An Excel spreadsheet for the decomposition of a difference between two values of an aggregate demographic measure by stepwise replacement running from young to old ages

A general algorithm for the decomposition of differences between two values of an aggregate demographic measure of age and other dimensions is realized as Excel/VBA. It assumes that the aggregate measure is computed from similar matrices of discrete demographic data for two populations under comparison. The algorithm estimates the effects of replacement for each elementary cell of one matrix by the respective cell of another matrix. The replacement runs from young to old ages. Background Comparative analyses often look at the differences between the values of aggregate demographic measures calculated for two populations. Once a difference has been identified, the researcher may be interested in decomposing it according to the effects of underlying factors, such as age group, sex, and cause of death. The general decomposition problem is to estimate the additive contributions of the differences between the values of the factors to the overall difference between the values of the aggregate measure. The aggregate measure of interest is considered as a dependent function of the factors. In 2002, we proposed a general algorithm for solving the decomposition problem by means of stepwise replacement (Andreev, Shkolnikov, Begun, 2002). For some aggregate measures, this algorithm produces formulae for the decomposition (Andreev, 1982; Pollard, 1982; Arriaga, 1984; Pressat, 1985; Das Gupta, 1994; Das Gupta, 1999; Shkolnikov, Andreev, and Begun, 2001, 2003). In many cases, however, a direct numerical application of the replacement algorithm has to be performed. For example, the existing formula for the decomposition of a difference between two life expectancies by ages and causes of death degenerates if at one or more ages the allcause death rates in the two populations are exactly the same. For many aggregate measures, the analytical expression for the decomposition either cannot be deduced, or it can be deduced, but is too complicated. For example, the formal expression of the age decomposition of a difference between e-dagger values (Vaupel, Canudas Romo, 2003) appears to be very complex (Shkolnikov, Andreev, Zhang, Vaupel, 2011). The numerical algorithm for decomposition by stepwise replacements is described in detail (Andreev, Shkolnikov, Begun, 2002). The “Decomposition_replacement.xls” spreadsheet produces exactly the algorithm that was described in the article. The replacement runs from young to old ages, and in each age group, the program performs all of the possible sequences of replacements. How to use it The Decomposition_replacement.xls spreadsheet consists of four worksheets: “C,” “Data-1,” “Data-2,” “Result,” and the VBA program “Decomp.” Specify the number of age groups and the number of variables at each age in cells B2 and B3 of worksheet C, respectively. The total number of age groups (rows) should be less than 120, and the number of variables at each age should be less than 11.